Skip Headers

Oracle® Data Provider for .NET Developer's Guide 10g Release 2 (10.2) Part Number B14307-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# OracleTransaction Class

An `OracleTransaction` object represents a local transaction.

Class Inheritance

`Object`

`MarshalByRefObject`

`OracleTransaction`

Declaration

    // C#
    public sealed class OracleTransaction : MarshalByRefObject,
       IDbTransaction, IDisposable

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

The application calls `BeginTransaction` on the `OracleConnection` object to create an `OracleTransaction` object. The `OracleTransaction` object can be created in one of the following two modes:

- Read Committed (default)

- Serializable

Any other mode results in an exception.

The execution of a DDL statement in the context of a transaction is not recommended since it results in an implicit commit that is not reflected in the state of the `OracleTransaction` object.

All operations related to savepoints pertain to the current local transaction. Operations like commit and rollback performed on the transaction have no effect on data in any existing `DataSet`.

Example

    // Database Setup, if you have not done so yet.
    /*
    connect scott/tiger@oracle
    DROP TABLE MyTable;
    CREATE TABLE MyTable (MyColumn NUMBER);
    --CREATE TABLE MyTable (MyColumn NUMBER PRIMARY KEY);

    */

    // C#

    using System;
    using System.Data;
    using Oracle.DataAccess.Client;

    class OracleTransactionSample
    {
      static void Main()
      {
        // Drop & Create MyTable as indicated Database Setup, at beginning

        // This sample starts a transaction and inserts two records with the same
        // value for MyColumn into MyTable.
        // If MyColumn is not a primary key, the transaction will commit.
        // If MyColumn is a primary key, the second insert will violate the
        // unique constraint and the transaction will rollback.

        string constr = "User Id=scott;Password=tiger;Data Source=oracle";
        OracleConnection con = new OracleConnection(constr);
        con.Open();

        OracleCommand cmd = con.CreateCommand();

        // Check the number of rows in MyTable before transaction
        cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
        int myTableCount = int.Parse(cmd.ExecuteScalar().ToString());

        // Print the number of rows in MyTable
        Console.WriteLine("myTableCount = " + myTableCount);

        // Start a transaction
        OracleTransaction txn = con.BeginTransaction(
          IsolationLevel.ReadCommitted);

        try
        {
          // Insert the same row twice into MyTable
          cmd.CommandText = "INSERT INTO MyTable VALUES (1)";
          cmd.ExecuteNonQuery();
          cmd.ExecuteNonQuery(); // This may throw an exception
          txn.Commit();
        }
        catch (Exception e)
        {
          // Print the exception message
          Console.WriteLine("e.Message    =  " + e.Message);

          // Rollback the transaction
          txn.Rollback();
        }

        // Check the number of rows in MyTable after transaction
        cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
        myTableCount = int.Parse(cmd.ExecuteScalar().ToString());

        // Prints the number of rows
        // If MyColumn is not a PRIMARY KEY, the value should increase by two.
        // If MyColumn is a PRIMARY KEY, the value should remain same.
        Console.WriteLine("myTableCount = " + myTableCount);

        txn.Dispose();
        cmd.Dispose();

        con.Close();
        con.Dispose();
      }
    }

Requirements

Namespace: `Oracle.DataAccess.Client`

Assembly: `Oracle.DataAccess.dll`

Comment: Not supported in a .NET stored procedure

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Members OracleTransaction Static Methods OracleTransaction Properties

## OracleTransaction Members

`OracleTransaction` members are listed in the following tables:

OracleTransaction Static Methods

The `OracleTransaction` static method is listed in Table 5-96.

Table 5-96 OracleTransaction Static Method

Method | Description
--- | ---
`Equals` | Inherited from `Object` (Overloaded)

OracleTransaction Properties

`OracleTransaction` properties are listed in Table 5-97.

Table 5-97 OracleTransaction Properties

Name | Description
--- | ---
IsolationLevel | Specifies the isolation level for the transaction
Connection | Specifies the connection that is associated with the transaction

OracleTransaction Public Methods

`OracleTransaction` public methods are listed in Table 5-98.

Table 5-98 OracleTransaction Public Methods

Public Method | Description
--- | ---
Commit | Commits the database transaction
`CreateObjRef` | Inherited from `MarshalByRefObject`
Dispose | Frees the resources used by the `OracleTransaction` object
`Equals` | Inherited from `Object` (Overloaded)
`GetHashCode` | Inherited from `Object`
`GetLifetimeService` | Inherited from `MarshalByRefObject`
`GetType` | Inherited from `Object`
`InitializeLifetimeService` | Inherited from `MarshalByRefObject`
Rollback | Rolls back a database transaction (Overloaded)
Save | Creates a savepoint within the current transaction
`ToString` | Inherited from `Object`

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class

## OracleTransaction Static Methods

The `OracleTransaction` static method is listed in Table 5-99.

Table 5-99 OracleTransaction Static Method

Method | Description
--- | ---
`Equals` | Inherited from `Object` (Overloaded)

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

## OracleTransaction Properties

`OracleTransaction` properties are listed in Table 5-100.

Table 5-100 OracleTransaction Properties

Name | Description
--- | ---
IsolationLevel | Specifies the isolation level for the transaction
Connection | Specifies the connection that is associated with the transaction

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

### IsolationLevel

This property specifies the isolation level for the transaction.

Declaration

    // C#
    public IsolationLevel IsolationLevel {get;}

Property Value

`IsolationLevel`

Implements

`IDbTransaction`

Exceptions

`InvalidOperationException` - The transaction has already completed.

Remarks

Default = `IsolationLevel.ReadCommitted`

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

### Connection

This property specifies the connection that is associated with the transaction.

Declaration

    // C#
    public OracleConnection Connection {get;}

Property Value

`Connection`

Implements

`IDbTransaction`

Remarks

This property indicates the `OracleConnection` object that is associated with the transaction.

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

## OracleTransaction Public Methods

`OracleTransaction` public methods are listed in Table 5-101.

Table 5-101 OracleTransaction Public Methods

Public Method | Description
--- | ---
Commit | Commits the database transaction
`CreateObjRef` | Inherited from `MarshalByRefObject`
Dispose | Frees the resources used by the `OracleTransaction` object
`Equals` | Inherited from `Object` (Overloaded)
`GetHashCode` | Inherited from `Object`
`GetLifetimeService` | Inherited from `MarshalByRefObject`
`GetType` | Inherited from `Object`
`InitializeLifetimeService` | Inherited from `MarshalByRefObject`
Rollback | Rolls back a database transaction (Overloaded)
Save | Creates a savepoint within the current transaction
`ToString` | Inherited from `Object`

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

### Commit

This method commits the database transaction.

Declaration

    // C#
    public void Commit();

Implements

`IDbTransaction`

Exceptions

`InvalidOperationException` - The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.

Remarks

Upon a successful commit, the transaction enters a completed state.

Example

    // Database Setup, if you have not done so yet
    /*
    connect scott/tiger@oracle
    DROP TABLE MyTable;
    CREATE TABLE MyTable (MyColumn NUMBER);
    --CREATE TABLE MyTable (MyColumn NUMBER PRIMARY KEY);

    */

    // C#

    using System;
    using System.Data;
    using Oracle.DataAccess.Client;

    class CommitSample
    {
      static void Main()
      {
        // Drop & Create MyTable as indicated in Database Setup, at beginning

        // This sample starts a transaction and inserts two records with the same
        // value for MyColumn into MyTable.
        // If MyColumn is not a primary key, the transaction will commit.
        // If MyColumn is a primary key, the second insert will violate the
        // unique constraint and the transaction will rollback.

        string constr = "User Id=scott;Password=tiger;Data Source=oracle";
        OracleConnection con = new OracleConnection(constr);
        con.Open();

        OracleCommand cmd = con.CreateCommand();

        // Check the number of rows in MyTable before transaction
        cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
        int myTableCount = int.Parse(cmd.ExecuteScalar().ToString());

        // Print the number of rows in MyTable
        Console.WriteLine("myTableCount = " + myTableCount);

        // Start a transaction
        OracleTransaction txn = con.BeginTransaction(
          IsolationLevel.ReadCommitted);

        try
        {
          // Insert the same row twice into MyTable
          cmd.CommandText = "INSERT INTO MyTable VALUES (1)";
          cmd.ExecuteNonQuery();
          cmd.ExecuteNonQuery(); // This may throw an exception
          txn.Commit();
        }
        catch (Exception e)
        {
          // Print the exception message
          Console.WriteLine("e.Message    =  " + e.Message);

          // Rollback the transaction
          txn.Rollback();
        }

        // Check the number of rows in MyTable after transaction
        cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
        myTableCount = int.Parse(cmd.ExecuteScalar().ToString());

        // Prints the number of rows
        // If MyColumn is not a PRIMARY KEY, the value should increase by two.
        // If MyColumn is a PRIMARY KEY, the value should remain same.
        Console.WriteLine("myTableCount = " + myTableCount);

        txn.Dispose();
        cmd.Dispose();

        con.Close();
        con.Dispose();
      }
    }

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

### Dispose

This method frees the resources used by the `OracleTransaction` object.

Declaration

    // C#
    public void Dispose();

Implements

`IDisposable`

Remarks

This method releases both the managed and unmanaged resources held by the `OracleTransaction` object. If the transaction is not in a completed state, an attempt to rollback the transaction is made.

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

### Rollback

`Rollback` rolls back a database transaction.

Overload List:

- Rollback()

  This method rolls back a database transaction.

- Rollback(string)

  This method rolls back a database transaction to a savepoint within the current transaction.

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

### Rollback()

This method rolls back a database transaction.

Declaration

    // C#
    public void Rollback();

Implements

`IDbTransaction`

Exceptions

`InvalidOperationException` - The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.

Remarks

After a `Rollback()`, the `OracleTransaction` object can no longer be used because the `Rollback` ends the transaction.

Example

    // Database Setup, if you have not done so yet.
    /*
    connect scott/tiger@oracle
    DROP TABLE MyTable;
    CREATE TABLE MyTable (MyColumn NUMBER);

    */

    // C#

    using System;
    using System.Data;
    using Oracle.DataAccess.Client;

    class RollbackSample
    {
      static void Main()
      {
        // Drop & Create MyTable as indicated previously in Database Setup

        // This sample starts a transaction and inserts one record into MyTable.
        // It then rollsback the transaction, the number of rows remains the same

        string constr = "User Id=scott;Password=tiger;Data Source=oracle";
        OracleConnection con = new OracleConnection(constr);
        con.Open();

        OracleCommand cmd = con.CreateCommand();

        // Check the number of rows in MyTable before transaction
        cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
        int myTableCount = int.Parse(cmd.ExecuteScalar().ToString());

        // Print the number of rows in MyTable
        Console.WriteLine("myTableCount = " + myTableCount);

        // Start a transaction
        OracleTransaction txn = con.BeginTransaction(
          IsolationLevel.ReadCommitted);

        // Insert a row into MyTable
        cmd.CommandText = "INSERT INTO MyTable VALUES (1)";
        cmd.ExecuteNonQuery();

        // Rollback the transaction
        txn.Rollback();

        // Check the number of rows in MyTable after transaction
        cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
        myTableCount = int.Parse(cmd.ExecuteScalar().ToString());

        // Prints the number of rows, should remain the same
        Console.WriteLine("myTableCount = " + myTableCount);

        txn.Dispose();
        cmd.Dispose();

        con.Close();
        con.Dispose();
      }
    }

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

### Rollback(string)

This method rolls back a database transaction to a savepoint within the current transaction.

Declaration

    // C#
    public void Rollback(string savepointName);

Parameters

- `savepointName`

  The name of the savepoint to rollback to, in the current transaction.

Exceptions

`InvalidOperationException` - The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.

Remarks

After a rollback to a savepoint, the current transaction remains active and can be used for further operations.

The `savepointName` specified does not have to match the case of the `savepointName` created using the `Save` method, since savepoints are created in the database in a case-insensitive manner.

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members

### Save

This method creates a savepoint within the current transaction.

Declaration

    // C#
    public void Save(string savepointName);

Parameters

- `savepointName`

  The name of the savepoint being created in the current transaction.

Exceptions

`InvalidOperationException` - The transaction has already been completed.

Remarks

After creating a savepoint, the transaction does not enter a completed state and can be used for further operations.

The `savepointName` specified is created in the database in a case-insensitive manner. Calling the `Rollback` method rolls back to `savepointName`. This allows portions of a transaction to be rolled back, instead of the entire transaction.

Example

    // Database Setup, if you have not done so yet.
    /*
    connect scott/tiger@oracle
    DROP TABLE MyTable;
    CREATE TABLE MyTable (MyColumn NUMBER);

    */

    // C#

    using System;
    using System.Data;
    using Oracle.DataAccess.Client;

    class SaveSample
    {
      static void Main()
      {
        // Drop & Create MyTable as indicated in Database Setup, at beginning

        // This sample starts a transaction and creates a savepoint after
        // inserting one record into MyTable.
        // After inserting the second record it rollsback to the savepoint
        // and commits the transaction. Only the first record will be inserted

        string constr = "User Id=scott;Password=tiger;Data Source=oracle";
        OracleConnection con = new OracleConnection(constr);
        con.Open();

        OracleCommand cmd = con.CreateCommand();

        // Check the number of rows in MyTable before transaction
        cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
        int myTableCount = int.Parse(cmd.ExecuteScalar().ToString());

        // Print the number of rows in MyTable
        Console.WriteLine("myTableCount = " + myTableCount);

        // Start a transaction
        OracleTransaction txn = con.BeginTransaction(
          IsolationLevel.ReadCommitted);

        // Insert a row into MyTable
        cmd.CommandText = "INSERT INTO MyTable VALUES (1)";
        cmd.ExecuteNonQuery();

        // Create a savepoint
        txn.Save("MySavePoint");

        // Insert another row into MyTable
        cmd.CommandText = "insert into mytable values (2)";
        cmd.ExecuteNonQuery();

        // Rollback to the savepoint
        txn.Rollback("MySavePoint");

        // Commit the transaction
        txn.Commit();

        // Check the number of rows in MyTable after transaction
        cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
        myTableCount = int.Parse(cmd.ExecuteScalar().ToString());

        // Prints the number of rows, should have increased by 1
        Console.WriteLine("myTableCount = " + myTableCount);

        txn.Dispose();
        cmd.Dispose();

        con.Close();
        con.Dispose();
      }
    }

See Also: "Oracle.DataAccess.Client Namespace" OracleTransaction Class OracleTransaction Members